from common import CommitteeEntity,SearchFilter
import DatabaseConnection

class CommitteeData(object):
    def __init__(self):
        pass

    def InsertCommittee(self,committee):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsCommittee',(committee.Name,committee.Chair))
        cursor.close()
        conn.commit()
        conn.close()

    def UpdateCommittee(self,committee):
	conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdCommittee',(committee.Name,committee.Chair))
        cursor.close()
        conn.commit()
        conn.close()

    def DeleteCommittee(self,ID): 
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelCommittee',(ID,)) #ID is the committee name
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type CommitteeEntity or None (if there is no record with this ID)
    def SelectCommitteeByID(self,ID):
        query = """ SELECT Name,Chair,ChairName
                    FROM vwCommittee
                    WHERE Name='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return CommitteeEntity.CommiteeEntity(name=result[0][0],chair=result[0][1],chairName=result[0][2])
        else:
            return None

        return None

    #This method returns a list of objects of type CommitteeEntity
    def SelectCommitteeByFilter(self,searchFilter):
        query = """ SELECT Name,Chair,ChairName
                    FROM vwCommittee"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(MemberEntity.MemberEntity(name=record[0],chair=record[1],chairName=record[2]))
        return recordSet

    def SelectAllCommittees(self):
        query = """ SELECT Name,Chair,ChairName
                    FROM vwCommittee"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(MemberEntity.MemberEntity(name=record[0],chair=record[1],chairName=record[2]))
        return recordSet
